R

To generate a scatter plot in R, use the geom_point() function. Here is how I would show the relationship between sales price and distance to the city.

ggplot(my_data,
       aes(x = city_dist,
           y = `sales-price`)) +
  geom_point()

A couple of things I notice about that plot. First of all, for both variables, it looks like there are a lot of lower values and fewer higher values. This might suggest that a log scale on both axes might make it easier to see the relationship between these two variables.

I can transform the x or y axis by setting the transform parameter in the scale_x_continous() or scale_y_continuous() functions. While I’m at it, I can also name those axes to be something more descriptive.

ggplot(my_data,
       aes(x = city_dist,
           y = `sales-price`)) +
  geom_point() +
  scale_x_continuous(name = "Distance to city (miles)",
                     transform = "log10") +
  scale_y_continuous(name = "Sales price",
                     transform = "log10") 

One more change I might want to make to the y-axis is to format display of the numbers. This is another thing I can do within the scale_y_continuous function.

ggplot(my_data,
       aes(x = city_dist,
           y = `sales-price`)) +
  geom_point() +
  scale_x_continuous(name = "Distance to city (miles)",
                     transform = "log10") +
  scale_y_continuous(name = "Sales price",
                     transform = "log10",
                     breaks = c(10000,
                                100000,
                                1000000),
                     labels = c("$10,000",
                                "$100,000",
                                "$1,000,000")) 

Finally, this cloud of points just looks like a big black blob, with lots of points on top of one another. I can get a better sense of what’s going on by adjusting the point transparency. I would do this by setting the alpha value within the geom_point function. I can also address the overplotting problem by making the points smaller (using the size parameter).

While I’m messing around the the appearance of the points, I might change the color as well.

ggplot(my_data,
       aes(x = city_dist,
           y = `sales-price`)) +
  geom_point(alpha = 0.3,
             size = 0.5,
             color = "orange") +
  scale_x_continuous(name = "Distance to city (miles)",
                     transform = "log10") +
  scale_y_continuous(name = "Sales price",
                     transform = "log10",
                     breaks = c(10000,
                                100000,
                                1000000),
                     labels = c("$10,000",
                                "$100,000",
                                "$1,000,000")) 

Excel

In Excel, you would create a scatter plot by selecting columns for the two variables you want to include (hold down that ctrl or cmd key while clicking on each column heading), and then click the picture of a little scatter plot on the Chart section of the Insert ribbon.

As we say in the R example, both variables, have a lot of lower values and fewer higher values. This might suggest that a log scale on both axes might make it easier to see the relationship between these two variables.

You can transform the axis on a scatter plot by opening the “Format axis” panel and checking the box for “Logarithmic scale.” You might also want to adjust the minimum value and the value where the alternative axis crosses.

You can also delete the chart title and add axis labels. To change the way the numbers are displayed on the axis, you can just change their display in the spreadsheet.

And you can also change how the points are displayed, which could help with your overplotting issues.

R

In R, you can add a linear trend line using the function stat_smooth(), setting method = "lm". You can also specify the color of the line and the fill color for the confidence interval (both will be blue by default).

ggplot(my_data,
       aes(x = city_dist,
           y = `sales-price`)) +
  geom_point(alpha = 0.3,
             size = 0.5,
             color = "orange") +
  scale_x_continuous(name = "Distance to city (miles)",
                     transform = "log10") +
  scale_y_continuous(name = "Sales price",
                     transform = "log10",
                     breaks = c(10000,
                                100000,
                                1000000),
                     labels = c("$10,000",
                                "$100,000",
                                "$1,000,000"))  +
  stat_smooth(method = "lm",
              color = "brown",
              fill = "brown")

## `geom_smooth()` using formula = 'y ~ x'

Excel

In Excel, you can you can add a linear trend line by clicking the “Add chart element” button.

In that example, the trend line doesn’t look like a straight line because the y-axis is on a log scale, and the line is referencing the underlying data rather than the displayed data. To get to look more like a straight line, you’d actually want to select “logarithmic” as the trend line type.

Note that this trend line in the above example still doesn’t look entirely straight. This is because setting the trend line type to “Logarithmic” will account for the log transformation on the x-axis, but not on the y-axis. To account for the log-transformation on the y-axis, you’d need to actually create a log-transformed version of your variable.

R

In R, you can use geom_jitter for a scatter plot where one of the values is categorical.

ggplot(my_data,
       aes(x = building,
           y = `sales-price`)) +
  geom_jitter(alpha = 0.3,
             size = 0.5,
             color = "orange") +
  scale_y_continuous(name = "Sales price",
                     transform = "log10",
                     breaks = c(10000,
                                100000,
                                1000000),
                     labels = c("$10,000",
                                "$100,000",
                                "$1,000,000"))  

Excel

There isn’t an easy way to do this in Excel - you need to manually create a variable to indicate where you want the points to be positioned on the x-axis.

In this example, I’m using an if statement to set the value to 1.0 plus some random variation if the building category is “Single-family”, to 2.5 plus some random variation if the building category is “Duplex”, and to 4.0 plus some random variation if the building category is anything else (the remaining value is “Three-plus”).

Then I can make a scatter plot with this new variable.

You’ll want to turn off the x-axis in this case since the numeric values are meaningless and you can manually add text boxes to indicate the categories.

R

In R, you can make a box plot useing the function geom_boxplot().

ggplot(my_data,
       aes(x = building,
           y = `sales-price`)) +
  geom_boxplot() +
  scale_y_continuous(name = "Sales price",
                     transform = "log10",
                     breaks = c(10000,
                                100000,
                                1000000),
                     labels = c("$10,000",
                                "$100,000",
                                "$1,000,000"))  

It could be helpful to overlay the box plot on a scatter plot. This functions like a trend line in the sense that you can see the actual data along with a shape that emphasizes relationships that are not obvious from the scatterplot alone.

ggplot(my_data,
       aes(x = building,
           y = `sales-price`)) +
  geom_jitter(alpha = 0.3,
             size = 0.5,
             color = "orange") +
  geom_boxplot(fill = NA) +
  scale_y_continuous(name = "Sales price",
                     transform = "log10",
                     breaks = c(10000,
                                100000,
                                1000000),
                     labels = c("$10,000",
                                "$100,000",
                                "$1,000,000"))  

A violin plot might be more intuitive than a box plot, and you can create one using geom_violin().As a default, a violin plot is just a density curve, where width of the shape is proportional to the number of observations with that value.

ggplot(my_data,
       aes(x = building,
           y = `sales-price`)) +
  geom_jitter(alpha = 0.3,
             size = 0.5,
             color = "orange") +
  geom_violin(fill = NA) +
  scale_y_continuous(name = "Sales price",
                     transform = "log10",
                     breaks = c(10000,
                                100000,
                                1000000),
                     labels = c("$10,000",
                                "$100,000",
                                "$1,000,000"))  

You can also specify that you want to add lines representing the values for specified quantiles. Here is how you would add lines for the 25th, 50th, and 75th percentile values.

ggplot(my_data,
       aes(x = building,
           y = `sales-price`)) +
  geom_jitter(alpha = 0.3,
             size = 0.5,
             color = "orange") +
  geom_violin(fill = NA,
              draw_quantiles = c(0.25, 0.5, 0.75)) +
  scale_y_continuous(name = "Sales price",
                     transform = "log10",
                     breaks = c(10000,
                                100000,
                                1000000),
                     labels = c("$10,000",
                                "$100,000",
                                "$1,000,000"))  

Excel

There isn’t an easy way to create a violin plot in Excel, but you can create a box-and whisker plot.

There is not an easy way to log-transform the numeric axis. You would need to create a log-transformed version of the variable.